﻿using PayexpDAL;
using SpreadsheetLight;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Utilities;

namespace ExcelHandler
{
    public abstract class ExportToExcelBase
    {
        protected string _destinationFileFullPath;

        protected bool _batchMode;

        protected SLDocument _slDocument;

        protected List<TransactionMaster> _listToExport;

        protected FilterCriteria _usedFilters;

        protected int _currentDataRowIndex;

        protected HeaderCellStyle _headerCellStyle;
        protected IncomeDataCellStyle _incomeDataCellStyle;
        protected IncomeDataCellStyleDeleted _incomeDataCellStyleDeleted;
        protected ExpenseDataCellStyle _expenseDataCellStyle;
        protected ExpenseDataCellStyleDeleted _expenseDataCellStyleDeleted;
        protected SummaryKeyStyle _summaryKeyStyle;
        protected SummaryValueStyle _summaryValueStyle;

        public ExportToExcelBase(string destinationFileFullPath, bool batchMode)
        {
            this._destinationFileFullPath = destinationFileFullPath;
            this._batchMode = batchMode;
            this._slDocument = new SLDocument();
            this._listToExport = new List<TransactionMaster>();

            this._headerCellStyle = new HeaderCellStyle();
            this._incomeDataCellStyle = new IncomeDataCellStyle();
            this._incomeDataCellStyleDeleted = new IncomeDataCellStyleDeleted();
            this._expenseDataCellStyle = new ExpenseDataCellStyle();
            this._expenseDataCellStyleDeleted = new ExpenseDataCellStyleDeleted();
            this._summaryKeyStyle = new SummaryKeyStyle();
            this._summaryValueStyle = new SummaryValueStyle();

            // default values
            this._currentDataRowIndex = 1;

            SLPageSettings slPageSettings = new SLPageSettings();
            slPageSettings.ZoomScale = 80;
            this._slDocument.SetPageSettings(slPageSettings);

        }

        public virtual void Export(List<TransactionMaster> transactionMasterList)
        {
            if (this._batchMode)
            {
                throw new Exception("Since you have set batchMode = true, you need to call the method ExportInBatches. Call CloseBatch when done to save the file to disk.");
            }

            if (transactionMasterList.Count > 500)
            {
                throw new Exception("Please use the method ExportInBatches if you are trying to export more than 500 rows at a time.");
            }
        }

        public virtual void ExportInBatches(List<TransactionMaster> transactionMasterList)
        {
            if (!this._batchMode)
            {
                throw new Exception("Please use the method Export since you have not set batchMode to true.");
            }

            WriteTransactionData(transactionMasterList);

        }

        public void CloseBatch()
        {
            this._slDocument.SaveAs(this._destinationFileFullPath);
        }


        protected virtual void CreateSummary()
        {
            string rowIndexString = this._currentDataRowIndex.ToString();
            this._slDocument.MergeWorksheetCells("A" + rowIndexString, "B" + rowIndexString);
            this._slDocument.MergeWorksheetCells("C" + rowIndexString, "E" + rowIndexString);
            
            this._slDocument.SetCellValue("A" + rowIndexString, "Report Created On");
            this._slDocument.SetCellStyle("A" + rowIndexString, "B" + rowIndexString, this._summaryKeyStyle);

            
            string cellValue = DateTime.Now.Get_DD_MONTH_YY_DateString() + " " + string.Format("{0:HH:mm:ss tt}", DateTime.Now);
            this._slDocument.SetCellValue("C" + rowIndexString, cellValue);
            this._slDocument.SetCellStyle("C" + rowIndexString, "E" + rowIndexString, this._summaryValueStyle);
            

            // setting the legend
            this._slDocument.MergeWorksheetCells("G" + rowIndexString, "J" + rowIndexString);
            this._slDocument.SetCellValue("G" + rowIndexString, "Legend");
            this._slDocument.SetCellStyle("G" + rowIndexString, "H" + rowIndexString, new LegendKeyStyle());

            rowIndexString = (this._currentDataRowIndex + 1).ToString();
            this._slDocument.MergeWorksheetCells("G" + rowIndexString, "H" + rowIndexString);
            this._slDocument.SetCellValue("G" + rowIndexString, "Income Records");
            this._slDocument.SetCellStyle("G" + rowIndexString, "H" + rowIndexString, new IncomeLegendValueStyle());

            this._slDocument.MergeWorksheetCells("I" + rowIndexString, "J" + rowIndexString);
            this._slDocument.SetCellValue("I" + rowIndexString, "Expense Records");
            this._slDocument.SetCellStyle("I" + rowIndexString, "J" + rowIndexString, new ExpenseLegendValueStyle());

        }

        protected void CreateDataRowsHeader()
        {
            this._currentDataRowIndex++; // VERY IMPORTANT! :)
            string rowIndexString = this._currentDataRowIndex.ToString();

            this._slDocument.SetCellValue("A" + rowIndexString, "Mode");
            this._slDocument.SetCellStyle("A" + rowIndexString, this._headerCellStyle);
            this._slDocument.SetColumnWidth("A", CellWidthConstants.TransactionModeWidth);

            this._slDocument.SetCellValue("B" + rowIndexString, "Transaction Date");
            this._slDocument.SetCellStyle("B" + rowIndexString, this._headerCellStyle);
            this._slDocument.SetColumnWidth("B", CellWidthConstants.TransactionDateWidth);

            this._slDocument.SetCellValue("C" + rowIndexString, "Transaction Bank");
            this._slDocument.SetCellStyle("C" + rowIndexString, this._headerCellStyle);
            this._slDocument.SetColumnWidth("C", CellWidthConstants.TransactionBankWidth);

            this._slDocument.SetCellValue("D" + rowIndexString, "Bank Date");
            this._slDocument.SetCellStyle("D" + rowIndexString, this._headerCellStyle);
            this._slDocument.SetColumnWidth("D", CellWidthConstants.BankDateWidth);

            this._slDocument.SetCellValue("E" + rowIndexString, "Amount");
            this._slDocument.SetCellStyle("E" + rowIndexString, this._headerCellStyle);
            this._slDocument.SetColumnWidth("E", CellWidthConstants.AmountWidth);

            this._slDocument.SetCellValue("F" + rowIndexString, "Running Balance");
            this._slDocument.SetCellStyle("F" + rowIndexString, this._headerCellStyle);
            this._slDocument.SetColumnWidth("F", CellWidthConstants.LedgerBalanceWidth);

            this._slDocument.SetCellValue("G" + rowIndexString, "Cheque No");
            this._slDocument.SetCellStyle("G" + rowIndexString, this._headerCellStyle);
            this._slDocument.SetColumnWidth("G", CellWidthConstants.ChequeNoWidth);

            this._slDocument.SetCellValue("H" + rowIndexString, "Cheque Drawn On");
            this._slDocument.SetCellStyle("H" + rowIndexString, this._headerCellStyle);
            this._slDocument.SetColumnWidth("H", CellWidthConstants.ChequeDrawnOnBankWidth);

            this._slDocument.SetCellValue("I" + rowIndexString, "Cheque Date");
            this._slDocument.SetCellStyle("I" + rowIndexString, this._headerCellStyle);
            this._slDocument.SetColumnWidth("I", CellWidthConstants.ChequeDateWidth);

            this._slDocument.SetCellValue("J" + rowIndexString, "Narration");
            this._slDocument.SetCellStyle("J" + rowIndexString, this._headerCellStyle);
            this._slDocument.SetColumnWidth("J", CellWidthConstants.NarrationWidth);

            this._slDocument.SetCellValue("K" + rowIndexString, "Head Details");
            this._slDocument.SetCellStyle("K" + rowIndexString, this._headerCellStyle);
            this._slDocument.SetColumnWidth("K", CellWidthConstants.HeadDetailsWidth);

            this._slDocument.SetCellValue("L" + rowIndexString, "Expense Approver(s)");
            this._slDocument.SetCellStyle("L" + rowIndexString, this._headerCellStyle);
            this._slDocument.SetColumnWidth("L", CellWidthConstants.HeadDetailsWidth);

            this._slDocument.SetCellValue("M" + rowIndexString, "Building");
            this._slDocument.SetCellStyle("M" + rowIndexString, this._headerCellStyle);
            this._slDocument.SetColumnWidth("M", CellWidthConstants.BuildingWidth);

            this._slDocument.SetCellValue("N" + rowIndexString, "Flat No");
            this._slDocument.SetCellStyle("N" + rowIndexString, this._headerCellStyle);
            this._slDocument.SetColumnWidth("N", CellWidthConstants.FlatNoWidth);

            this._slDocument.SetCellValue("O" + rowIndexString, "Receipt/ Voucher No");
            this._slDocument.SetCellStyle("O" + rowIndexString, this._headerCellStyle);
            this._slDocument.SetColumnWidth("O", CellWidthConstants.ReceiptVoucherNoWidth);

            this._slDocument.SetCellValue("P" + rowIndexString, "Transaction Id");
            this._slDocument.SetCellStyle("P" + rowIndexString, this._headerCellStyle);
            this._slDocument.SetColumnWidth("P", CellWidthConstants.TransactionIdWidth);

            this._slDocument.SetCellValue("Q" + rowIndexString, "Created By");
            this._slDocument.SetCellStyle("Q" + rowIndexString, this._headerCellStyle);
            this._slDocument.SetColumnWidth("Q", CellWidthConstants.CreatedByWidth);

            this._slDocument.SetCellValue("R" + rowIndexString, "Updated By");
            this._slDocument.SetCellStyle("R" + rowIndexString, this._headerCellStyle);
            this._slDocument.SetColumnWidth("R", CellWidthConstants.UpdatedByWidth);
        }

        protected void WriteTransactionData(List<TransactionMaster> transactionMasterList)
        {
            foreach (TransactionMaster transactionMaster in transactionMasterList)
            {

                ExcelStyle styleToBeUsed = null;
                if (transactionMaster.Head.Equals("head_expense"))
                {
                    if (transactionMaster.IsActive)
                    {
                        styleToBeUsed = this._expenseDataCellStyle;
                    }
                    else
                    {
                        styleToBeUsed = this._expenseDataCellStyleDeleted;
                    }
                }
                else
                {
                    if (transactionMaster.IsActive)
                    {
                        styleToBeUsed = this._incomeDataCellStyle;
                    }
                    else
                    {
                        styleToBeUsed = this._incomeDataCellStyleDeleted;
                    }
                }

                this._currentDataRowIndex++; // VERY IMPORTANT! :)
                string rowIndexString = this._currentDataRowIndex.ToString();


                this._slDocument.SetCellValue("A" + rowIndexString, transactionMaster.TransactionModeValueDisplayName);
                this._slDocument.SetCellStyle("A" + rowIndexString, styleToBeUsed);
                
                this._slDocument.SetCellValue("B" + rowIndexString, transactionMaster.TransactionDate);
                SetDateTimeFormatCode(styleToBeUsed);
                this._slDocument.SetCellStyle("B" + rowIndexString, styleToBeUsed);
                ResetFormatCode(styleToBeUsed);
                

                if (transactionMaster.BankAccount != null)
                {
                    this._slDocument.SetCellValue("C" + rowIndexString, transactionMaster.BankAccount.BankDisplayName);
                }
                this._slDocument.SetCellStyle("C" + rowIndexString, styleToBeUsed);

                SetDateTimeFormatCode(styleToBeUsed);
                if (transactionMaster.BankDate.HasValue)
                {
                    this._slDocument.SetCellValue("D" + rowIndexString, transactionMaster.BankDate.Value);
                }
                this._slDocument.SetCellStyle("D" + rowIndexString, styleToBeUsed);
                ResetFormatCode(styleToBeUsed);

                SetDecimalFormatCode(styleToBeUsed);
                this._slDocument.SetCellValue("E" + rowIndexString, transactionMaster.TransactionAmount);
                this._slDocument.SetCellStyle("E" + rowIndexString, styleToBeUsed);
                ResetFormatCode(styleToBeUsed);

                SetDecimalFormatCode(styleToBeUsed);
                this._slDocument.SetCellStyle("F" + rowIndexString, styleToBeUsed);
                if (this._usedFilters != null && this._usedFilters.RunningBalanceComputed)
                {
                    this._slDocument.SetCellValue("F" + rowIndexString, transactionMaster.CurrentBalance);
                }
                else
                {
                    if (transactionMaster.BankDate.HasValue)
                    {
                        this._slDocument.SetCellValue("F" + rowIndexString, transactionMaster.CurrentBalance);
                    }
                }
                ResetFormatCode(styleToBeUsed);

                if (transactionMaster.ChequeNumber != 0)
                {
                    this._slDocument.SetCellValue("G" + rowIndexString, transactionMaster.ChequeNumber);
                }
                this._slDocument.SetCellStyle("G" + rowIndexString, styleToBeUsed);

                this._slDocument.SetCellValue("H" + rowIndexString, transactionMaster.ChequeDrawnOnBank);
                this._slDocument.SetCellStyle("H" + rowIndexString, styleToBeUsed);


                SetDateTimeFormatCode(styleToBeUsed);
                if (transactionMaster.ChequeDate.HasValue)
                {
                    this._slDocument.SetCellValue("I" + rowIndexString, transactionMaster.ChequeDate.Value);
                }
                this._slDocument.SetCellStyle("I" + rowIndexString, styleToBeUsed);
                ResetFormatCode(styleToBeUsed);

              
                this._slDocument.SetCellValue("J" + rowIndexString, transactionMaster.Narration);
                this._slDocument.SetCellStyle("J" + rowIndexString, styleToBeUsed);

                this._slDocument.SetCellValue("K" + rowIndexString, transactionMaster.GetHeadDetailsComplete());
                this._slDocument.SetCellStyle("K" + rowIndexString, styleToBeUsed);

                this._slDocument.SetCellValue("L" + rowIndexString, transactionMaster.ExpenseApprovalDetails);
                this._slDocument.SetCellStyle("L" + rowIndexString, styleToBeUsed);
                
                this._slDocument.SetCellValue("M" + rowIndexString, transactionMaster.BuildingName);
                this._slDocument.SetCellStyle("M" + rowIndexString, styleToBeUsed);
                
                this._slDocument.SetCellValue("N" + rowIndexString, transactionMaster.FlatNumber);
                this._slDocument.SetCellStyle("N" + rowIndexString, styleToBeUsed);

                this._slDocument.SetCellValue("O" + rowIndexString, (transactionMaster.VoucherReceiptPrefix + transactionMaster.VoucherReceiptNumber.ToString()));
                this._slDocument.SetCellStyle("O" + rowIndexString, styleToBeUsed);

                this._slDocument.SetCellValue("P" + rowIndexString, transactionMaster.Id.ToString());
                this._slDocument.SetCellStyle("P" + rowIndexString, styleToBeUsed);

                this._slDocument.SetCellValue("Q" + rowIndexString, transactionMaster.CreatedBy.FirstLetterUpperCaseRestLowerCase());
                this._slDocument.SetCellStyle("Q" + rowIndexString, styleToBeUsed);

                this._slDocument.SetCellValue("R" + rowIndexString, transactionMaster.UpdatedBy.FirstLetterUpperCaseRestLowerCase());
                this._slDocument.SetCellStyle("R" + rowIndexString, styleToBeUsed);
            }
        }

        protected void ResetFormatCode(ExcelStyle excelStyle)
        {
            if (excelStyle != null)
            {
                excelStyle.FormatCode = string.Empty;
            }
        }

        protected void SetDateTimeFormatCode(ExcelStyle excelStyle)
        {
            excelStyle.FormatCode = "d mmm yyyy";
        }

        protected void SetDateTimeFormatCodeWithTimeValue(ExcelStyle excelStyle)
        {
            excelStyle.FormatCode = "d mmm yyyy hh:mm";
        }

        protected void SetDecimalFormatCode(ExcelStyle excelStyle)
        {
            excelStyle.FormatCode = "0.00";
        }
    }
}
